﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.SQLite
Imports System.IO

Public Class frmManutencaoBaseDados
    Private strQuery As String
    Private objToReturn As System.Windows.Forms.Control
    Private blnSLF, blnSWS As Boolean

    Public Property SQLQuery() As String
        Get
            Return strQuery
        End Get
        Set(ByVal value As String)
            strQuery = value
        End Set
    End Property

    Public Property ReturnTO() As System.Windows.Forms.Control
        Get
            Return objToReturn
        End Get
        Set(ByVal value As System.Windows.Forms.Control)
            objToReturn = value
        End Set
    End Property

    Private Sub ExecutaQueryNet(ByVal myGrid As AnguloGridView, ByVal myQuery As String)
        Call Application.DoEvents()
        Dim myConnection As New SQLiteConnection()
        Dim myDA As New SQLiteDataAdapter()
        Dim myDS As New DataSet()
        Dim myDR As SQLiteDataReader
        Dim myCMD As New SQLiteCommand()
        Try
            myConnection.ConnectionString = strMyDBString
            myConnection.Open()
            '--
            myCMD.Connection = myConnection
            myCMD.CommandText = myQuery
            '--
            myDR = myCMD.ExecuteReader()
            myDA = New SQLiteDataAdapter(myQuery, myConnection)
            '--
            myDS = New DataSet(0)
            '--
            myDA.FillSchema(myDS, SchemaType.Source, 0)
            myDA.Fill(myDS, 0)
            '--
            With myGrid
                .MultiSelect = False
                .VirtualMode = True
                '--
                .DataSource = myDS.Tables(0)  '<< retorno os dados
                ''-- column autosize
                For intCol As Integer = 0 To .Columns.Count - 1
                    .Columns(intCol).ReadOnly = True
                    .Columns(intCol).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
                Next
            End With
            myConnection.Close()
            tabQuery.SelectedIndex = 0
        Catch ex As Exception
            Call SendError(ex.Message, ex.StackTrace)
        Finally
            myConnection = Nothing
        End Try
    End Sub

    Private Sub txtSQL_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtSQL.KeyDown
        If e.KeyCode = Keys.F5 Then
            Call ExecutaQuery()
        End If
    End Sub

    Private Sub ExecutaQuery()
        Try
            Dim strSQL As String = ""
            Dim strDDL() As String
            If Not IsEmpty(txtSQL.Selection.Text.Trim) Then
                strSQL = txtSQL.Selection.Text.Trim
            Else
                strSQL = txtSQL.Text.Trim
            End If
            Me.SQLQuery = TrataSQL(strSQL)
            strDDL = Split(strSQL, " ")
            Select Case strDDL(0).ToUpper
                Case "SELECT"
                    Call ExecutaQueryNet(dgvResultado, Me.SQLQuery)
                Case Else
                    Call ExecutaCommando()
            End Select
        Catch ex As Exception
            Call MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        Finally
            txtSQL.Focus()
        End Try
    End Sub

    Private Sub ExecutaCommando()
        Dim strSQL As String = Me.SQLQuery
        Try
            Dim intRecords As Integer = mySQLite.ExecuteReturn(strSQL)

            dgvResultado.DataSource = Nothing
            stbRecordText.Text = intRecords & " registros afetados."
            txtMensagem.Text = "Comando executado com sucesso!" & _
                               vbCrLf & Strings.StrDup(30, "-") & vbCrLf & strSQL

        Catch exc As SQLiteException
            txtMensagem.Text = "Comando não foi executado e gerou um erro!" & _
                               vbCrLf & Strings.StrDup(42, "-") & vbCrLf & exc.Message & _
                               vbCrLf & Strings.StrDup(42, "-") & vbCrLf & strSQL
        Finally
            tabQuery.SelectedIndex = 1
            With txtMensagem
                .SelectionStart = 0
                .SelectionLength = 0
            End With
        End Try
    End Sub

    Private Sub frmQueryEditor_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If objToReturn Is Nothing Then
            '-- não retorna a nenhum objeto
        Else
            objToReturn.Text = txtSQL.Text
        End If
        txtSQL.FindReplace.Window.Close()
    End Sub

    Private Sub frmQueryEditor_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Call LoadTables()
        Call MontaGrid(dgvResultado)
        Call MontaGrid(dgvTables)
    End Sub

    Private Sub MontaGrid(ByVal myGrid As AnguloGridView)
        Try
            '-- DataGridView
            With myGrid
                .SelectionMode = DataGridViewSelectionMode.FullRowSelect
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToResizeRows = False
                '.AlternatingRowsDefaultCellStyle.BackColor = Color.DarkGray'-- zebrado
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .MultiSelect = False
            End With

        Catch ex As Exception
            Call SendError(ex.Message, ex.StackTrace)
        End Try
    End Sub

    Private Sub LoadTables()
        Dim strSQL As String = "SELECT name FROM sqlite_master WHERE type='table';"
        Call ExecutaQueryNet(dgvTables, strSQL)
    End Sub

    Private Sub tbSQL_ItemClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles tbSQL.ItemClicked
        Select Case e.ClickedItem.Tag
            Case "SLF" '--Show Line Feed
                If txtSQL.EndOfLine.IsVisible = True Then
                    txtSQL.EndOfLine.IsVisible = False
                Else
                    txtSQL.EndOfLine.IsVisible = True
                End If
            Case "SWS" '-- Show White Spaces
                If blnSWS = True Then
                    txtSQL.Whitespace.Mode = ScintillaNet.WhitespaceMode.Invisible
                    blnSWS = False
                Else
                    txtSQL.Whitespace.Mode = ScintillaNet.WhitespaceMode.VisibleAlways
                    blnSWS = True
                End If
        End Select
    End Sub

    Private Function TrataSQL(ByVal strSQLExec As String) As String
        Dim strSQLOut As String = strSQLExec
        Try
            Dim strNomeVariavel As String = "", strDefValue As String = ""
            Dim myDT As DataTable = mySQLite.QueryDataTable("SELECT nome_variavel, valor, descricao, tipo " & _
                                                            "FROM variaveis " & _
                                                            "ORDER BY nome_variavel ASC")
            If Not mySQLite.EOF Then
                For intRow As Integer = 0 To myDT.Rows.Count - 1
                    strNomeVariavel = "{@" & TrataNull(myDT.Rows(intRow).Item("nome_variavel"), TypeCode.String) & "}"
                    strDefValue = TrataNull(myDT.Rows(intRow).Item("valor"), TypeCode.String)
                    If InStr(strSQLExec, strNomeVariavel) > 0 Then
                        strSQLOut = Replace(strSQLOut, _
                                            strNomeVariavel, _
                                            frmQueryEditorVarStrings.ShowMe(strNomeVariavel, strDefValue))
                    End If
                Next
            End If
            Return strSQLOut
        Catch ex As Exception
            Call SendError(ex.Message, ex.StackTrace)
            Return strSQLOut
        End Try
    End Function

    Private Sub dgvTables_CellDoubleClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvTables.CellDoubleClick
        With dgvTables
            If Not IsEmpty(.TextMatrix(e.RowIndex, 0)) Then
                Call RetornaCreateTable(.TextMatrix(e.RowIndex, 0).ToString)
            End If
        End With
    End Sub

    Private Sub RetornaCreateTable(ByVal myTable As String)
        Dim myConnection As New SQLiteConnection()
        Dim strSQL As String = "SELECT sql " & _
                               "FROM sqlite_master " & _
                               "WHERE tbl_name = '" & myTable & "' " & _
                               "AND type = 'table'"
        Dim strDDL As String = ""
        Try
            myConnection.ConnectionString = strMyDBString
            myConnection.Open()

            Dim myCA As New SQLiteCommand(strSQL, myConnection)
            txtMensagem.Text = myCA.ExecuteScalar()
            tabQuery.SelectedIndex = 1
        Catch ex As Exception

        End Try
    End Sub

End Class